﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Web;
using OTS.BLL;
using System.Web.Configuration;

/// <summary>
/// Summary description for IncasementDAO
/// </summary>
public class IncasementDAO
{
	public IncasementDAO()
	{	}

    SqlHelp help = new SqlHelp();
	
    public DataTable GetContractNo(string strCondiction)
    {
        return help.GetDataTable("select distinct top 300 T.ContractNoMain from M_Contract C join M_SO_Trading T "
            + " on C.ContractNoMain=T.ContractNoMain and T.TradingSoNo is not null " + strCondiction);
    }
    
    public DataTable GetSoNoCustomerSaleName(string contractNo)
    {
        return help.GetDataTable("select distinct T.*,SC.SalesName from M_SO_Trading T "
            + " left join M_SalesCatalog SC on T.ContractNoMain=SC.ContractNoMain "
            + "  where T.ContractNoMain='" + contractNo + "'");
    }

    public int IncasementAdd(IncasementModel model)
    {
        SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@CoCode",model.CoCode),
            new SqlParameter("@IncasementType",model.IncasementType),
            new SqlParameter("@UserCode",model.UserCode),
            new SqlParameter("@ContractNoMain",model.ContractNoMain),
            new SqlParameter("@SONo",model.SoNo),
            new SqlParameter("@ContractNoCust",model.ContractNoCust),
            new SqlParameter("@AttachedFoldersName",model.AttachedFoldersName),
            new SqlParameter("@AttachedFolderCreateTime",model.AttachedFolderCreateTime),
            new SqlParameter("@date",model.date),
            new SqlParameter("@AttachedFileNamesNow",model.AttachedFileNamesNow),
            new SqlParameter("@AttachedFileNamesAll",model.AttachedFileNamesAll),
            new SqlParameter("@IncasementDesc",model.IncasementDesc)
        };
        return help.RunProcInt("IncasementAdd", paras);
    }

    public DataTable[] GetEmail(string contractNo, string userCode)
    {
        SqlParameter[] prams = new SqlParameter[] { new SqlParameter("@ContractNoMain", contractNo), new SqlParameter("@UserCode", userCode) };
        return help.RunProcGetTables("GetEmailForIncasement", prams);
    }

    public DataTable[] GetddlForIncasement()
    {
        return help.RunProcGetTables("GetddlForIncasement", null);
    }

    public DataTable IncasementUploadFile(IncasementModel model)
    {
        SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@UserCode",model.UserCode),
            new SqlParameter("@ContractNoMain",model.ContractNoMain),
            new SqlParameter("@SONo",model.SoNo),
            new SqlParameter("@ContractNoCust",model.ContractNoCust),
            new SqlParameter("@AttachedFileName",model.AttachedFileName),
            new SqlParameter("@AttachedFileCreateTime",model.date),
            new SqlParameter("@PlantSoNo",model.PlantSoNo)
        };
        return help.RunDataTableProc("IncasementUploadFile", paras);
    }

    public DataTable GetIncasementFiles(IncasementModel model)
    {
        SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@ContractNoMain",model.ContractNoMain),
            new SqlParameter("@SONo",model.SoNo),
            new SqlParameter("@PlantSoNo",model.PlantSoNo)
        };
        return help.RunDataTableProc("GetIncasementFiles", paras);
    }

    public DataTable GetPlantSoNoByTradingSoNo(IncasementModel model)
    {
        string sql = @"select distinct PlantSoNo
from	M_SO_Trading T 
		join M_SO_Plant P on (P.PlantCustomerPONo=T.TradingPO or cast(P.PlantCustomerPONo as bigint)=cast(T.TradingPO as bigint))
and (P.PlantLineNoForTranding=T.TradingPOLine or cast(P.PlantLineNoForTranding as bigint)=cast(T.TradingPOLine as bigint))
		and P.ContractNoMain=T.ContractNoMain
where T.ContractNoMain='{0}' and T.TradingSoNo like '%{1}%'";
        return help.GetDataTable(string.Format(sql, model.ContractNoMain, model.SoNo));
    }

    public DataTable GetTradingSoNoByPlantSoNo(IncasementModel model)
    {
        string sql = @"select distinct TradingSoNo
from	M_SO_Plant P
		join M_SO_Trading T on (P.PlantCustomerPONo=T.TradingPO or cast(P.PlantCustomerPONo as bigint)=cast(T.TradingPO as bigint))
and (P.PlantLineNoForTranding=T.TradingPOLine or cast(P.PlantLineNoForTranding as bigint)=cast(T.TradingPOLine as bigint))
		and P.ContractNoMain=T.ContractNoMain
where P.ContractNoMain='{0}' and P.PlantSoNo like '%{1}%'";
        return help.GetDataTable(string.Format(sql, model.ContractNoMain, model.PlantSoNo));
    }

    public DataTable[] GetIncasementInit(string contractNo, string soNo, string incasementNo)
    {
        SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@ContractNoMain",contractNo),
            new SqlParameter("@SONo",soNo),
            new SqlParameter("@IncasementNo",incasementNo)
        };
        return help.RunProcGetTables("GetIncasementInit", paras);
    }

    public bool ChechFileExit(string fileNamePath)
    {
        FileInfo fiL = new FileInfo(HttpContext.Current.Server.MapPath(fileNamePath));
        return fiL.Exists;
    }

    public DataTable IncasementQuery(string strWhere)
    {
        DataTable dt = null;

        string str = "select distinct T.ContractNoMain from M_SO_Trading T "
            + " where T.TradingSoNo is not null ";
        dt = help.GetDataTable(str + strWhere);

        return dt;
    }

    public int DeleteSentEmail(IncasementModel model)
    {
        #region 邮件发送

        DataTable[] dt = GetEmail(model.ContractNoMain, model.UserCode);
        if (dt != null && dt.Length > 1 && dt[0].Rows.Count > 0 && dt[1].Rows.Count > 0)
        {
            MailMessage mailObj = new MailMessage();
            string StrFrom = WebConfigurationManager.AppSettings["publicEmail"];
            string StrPwd = WebConfigurationManager.AppSettings["publicEmailPWD"];
            //发件人名
            string StrFromPerson = string.Empty;
            //收件人
            string StrToPerson = string.Empty;

            StrFromPerson = dt[0].Rows[0]["UserName"].ToString();

            // 添加发件人
            mailObj.From = new MailAddress(StrFrom);
            //添加邮件接收人                    
            foreach (DataRow row in dt[1].Rows)
            {
                mailObj.To.Add(row["Email"].ToString());
                StrToPerson += row["UserName"] + " ";
            }
            // 发邮件
            #region
            SentEmailModel sentEmailModel = new SentEmailModel();

            sentEmailModel.ContractNoMain = model.ContractNoMain;
            sentEmailModel.UserCode = model.UserCode;
            sentEmailModel.EmailTittl = model.EmailTittl;
            sentEmailModel.EmailBody = string.Format("<div><div style=\"height:15px;\"></div>"
                + "<div>Dear {0}</div> <br /><div>"
                    + " <table cellpadding=\"0\" cellspacing=\"0\">"
                    + " <tr><td style=\"height:22px; text-align:left;\">   {4}.</td></tr>"
                    + " <tr><td style=\"height:22px; text-align:left;\">Please check and Apply to Delivery in time!</td></tr>"
                     + "<tr><td style=\"height:22px; text-align:left;\">备注：{5}</td></tr>"
                     + "<tr><td style=\"height:22px; text-align:left;\"></td></tr>"
                     + "<tr><td style=\"height:22px; text-align:left;\">Best Regards.</td></tr>"
                    + " <tr><td style=\"height:22px; text-align:left;\"></td></tr>"
                     + "<tr><td style=\"height:22px; text-align:left;\">{3}</td></tr>"
                    + " </table></div></div>",
               StrToPerson, model.ContractNoMain, model.SoNo, StrFromPerson, model.EmailContent0, model.Remark);
            #endregion

            if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("00000"))
            { mailObj.CC.Add("tsu@shinfomelody.com"); }
            else if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("1"))
            {
                mailObj.CC.Add("Ocean.He@itt.com");
                mailObj.CC.Add("tsu@shinfomelody.com");
            }
            else if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("99999"))
            { mailObj.CC.Add("Ocean.He@itt.com"); }

            // 邮件内容
            mailObj.Subject = sentEmailModel.EmailTittl;
            mailObj.Body = sentEmailModel.EmailBody;
            try
            {
                SentEmailBLL.SentEmail(mailObj, StrFrom, StrPwd);

                #region SentEmailRecord
                sentEmailModel.ContractNoCust = model.ContractNoCust;
                sentEmailModel.EmailContent = sentEmailModel.EmailBody;
                sentEmailModel.EmailSentDate = model.date;
                sentEmailModel.EmailTitle = sentEmailModel.EmailTittl;
                sentEmailModel.EmailType = "Incasement";
                sentEmailModel.EmailTypeName = "装箱单汇总";
                sentEmailModel.UserCode = model.UserCode;
                sentEmailModel.StrToPerson = StrToPerson;
                SentEmailBLL.EmailRecord(sentEmailModel);
                #endregion

                return 1;
            }
            catch { return 0; }
        }
        else 
        { 
            CommonMethod.regAlertMessage("Can not Get Email Address!", model.page);
            return 0;
        }
        #endregion
    }
}